/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author XuanTuan
 */
public class CertificateDAO {

    public static boolean addCert(String serial, String username, Date notAfter, Date notBerfore, String path, String certName) {
        Connection con = ConnectDatabase.getConnection();
        try {
            String insert = "INSERT INTO certificate VALUES(?,?,?,?,?,?,?)";
            PreparedStatement sta = con.prepareStatement(insert);
            sta.setString(1, serial);
            sta.setString(2, username);
            sta.setDate(3, new java.sql.Date(notAfter.getTime()));
            sta.setDate(4, new java.sql.Date(notBerfore.getTime()));
            sta.setString(5, path);
            sta.setString(6, "In use");
            sta.setString(7, certName);
            int i = sta.executeUpdate();
            return (i == 1);
        } catch (SQLException ex) {
            Logger.getLogger(CertificateDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public static boolean deleteCert(String serial) {
        Connection con = ConnectDatabase.getConnection();
        try {
            String delete = "DELETE FROM certificate WHERE serial_num='" + serial + "'";
            Statement statement = con.createStatement();
            return statement.executeUpdate(delete) == 1;
        } catch (SQLException ex) {
            Logger.getLogger(CertificateDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public static ArrayList<Certificate> getCertByUser(String username) {
        Connection con = ConnectDatabase.getConnection();
        ArrayList<Certificate> array = new ArrayList<Certificate>();
        try {
            String select = "SELECT * FROM certificate WHERE `username`='" + username + "' ORDER BY serial_num DESC";
            Statement sta = con.createStatement();
            ResultSet set = sta.executeQuery(select);
            while (set.next()) {
                String serial = set.getString(1);
                java.sql.Date dateCreate = set.getDate(3);
                java.sql.Date dateExpire = set.getDate(4);
                String certPath = set.getString(5);
                String status = set.getString(6);
                String certName = set.getString(7);
                Certificate cert = new Certificate(serial, dateCreate, dateExpire, status, certPath, certName);
                array.add(cert);
            }
            return array;
        } catch (SQLException ex) {
            Logger.getLogger(CertificateDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public static boolean revokeCert(String serial) {
        Connection con = ConnectDatabase.getConnection();
        try {
            String revoke = "UPDATE certificate SET status='Revoked' WHERE serial_num='" + serial + "'";
            Statement statement = con.createStatement();
            return statement.executeUpdate(revoke) == 1;
        } catch (SQLException ex) {
            Logger.getLogger(CertificateDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

}
